package com.stb.llh.dao;

import com.stb.llh.entity.AddressCount;
import com.stb.llh.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;


/**
 * @Author Lee
 * @Date 2021/11/1
 * @Description
 */


//@Repository
//@EnableJpaRepositories
public interface UserDao extends JpaRepository<User,Integer>, CrudRepository<User,Integer> {


    /**
     * 通过用户名和密码登录
     * @param username
     * @param pwd
     * @return
     */
    User findUserByUsernameAndPwd(String username,String pwd);

    /**
     *
     * @param username 用户名
     * @return
     */
    User findUserByUsername(String username);





    @Query(value = "select count(*) from user",nativeQuery = true)
    int countAllByUsernameIsNotNull();


    @Query(value = "select  count(*) from user where DATEDIFF(DATE_FORMAT(dates,'%Y-%m-%d'),CURRENT_DATE) = 0 ",nativeQuery = true)
    int countUserNow();



    @Query(value = "select count(*) from user where sex='男' or sex='' ",nativeQuery = true)
    int maleCount();


    @Query(value = "select count(*) from user where sex='女' ",nativeQuery = true)
    int femaleCount();


    @Query(value = "select address, count(*) counts from user group by address ",nativeQuery = true)
    List<AddressCount> addressCount();

    @Query("SELECT " +
            "    new com.stb.llh.entity.AddressCount(v.address, COUNT(v)) " +
            "FROM " +
            "    User v " +
            "GROUP BY " +
            "    v.address "+
            " order by count(v) DESC ")
    List<AddressCount> getAddressCount();



}
